IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MapBudgetFeed]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MapBudgetFeed]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/***************************************************************************
* Name:			MapBudgetFeed
* Purpose:		Using table Stg_IE_AgrBudget_Ctl , map and validate
*				feed data.
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* 
* select * from Stg_IE_AgrBudget_Ctl 
* select * from Stg_IE_AgrBudgetInfo_Ctl 
***************************************************************************/
CREATE PROCEDURE [dbo].[MapBudgetFeed]
AS
BEGIN
	
		DECLARE @agrBudgetContainerViewIdSeed int
	DECLARE @agrBudgetIdSeed int
	DECLARE	@sql	varchar(1000)
	DECLARE @agrBudgetICRateIdSeed int
	DECLARE @agrBudgetItemIdSeed int
	DECLARE @agrBudgetContainerIdSeed int
	SET NOCOUNT ON

	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- G E T  E X I S T I N G  A G R E E M E N T  I D
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

	Update Stg_IE_Existing_Agreement
	SET
		AgrAgreementId = Id
	FROM 
		Stg_IE_Existing_Agreement  SEA
		inner join AgrAgreement a on SEA.Prop_No = A.PropNumber --a.FolderNumber = SEA.sFolderNumber 
--		and a.documentnumber = (SELECT max(a1.documentNumber) FROM AgrAgreement a1
--							WHERE a1.foldernumber = a.foldernumber 																
--							and a1.isAmendment = 0 and a1.IsCurrent=1)		

	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- B U D G E T  H E A D E R	
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- ADJUST AWARD TYPE
	-- In InfoEd there are three types of budgets Initial, Funding Increment and Adjustment
	-- Initial is assigned to the first budget year and Funding Increment is assigned to years 
	-- following first year. Both these types come to Insight as Annual. So we need to
	-- change all Funding Increment into Initial. We will do that in Insight_Awd_Type field
	-- and keep Orig_Awd_Type with original value. select * from Stg_IE_AgrBudget_Ctl 
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

	UPDATE Stg_IE_AgrBudget_Ctl 
	SET
		Insight_Awd_Type = 'Annual'
	WHERE
		Insight_Awd_Type = 'Funding Increment' 
		or Insight_Awd_Type = 'Initial'
		or Insight_Awd_Type = 'Restricted Supplement'

	UPDATE Stg_IE_AgrBudget_Ctl
	SET
		Insight_Awd_Type = 'Annual'
	WHERE
		Insight_Awd_Type is NULL
		--and BudgetView = 'Pending'
		
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- ADJUST FolderNumber
	--Remove dashes from Folder numbers.
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	
	UPDATE Stg_IE_AgrBudget_Ctl  
	SET 
		FolderNumber = (Replace(FolderNumber, '-', '')) 
	WHERE 
		foldernumber like '%-%'
	
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- ADJUST BUDGET VIEW, IsAwarded flag
	-- If Budget is Committed then in the feed IsAwarded is coming as 0
	-- and BudgetView is coming as Fully Executed/Awarded.
	-- We need to change IsAwarded to 1, BudgetView to Committed.
	-- Committed is a new BudgetView type in Insight.
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	UPDATE Stg_IE_AgrBudget_Ctl  
	SET 
		IsAwarded = 1,
		BudgetView = 'Committed'
	WHERE 
		sStatus = 'Committed'


	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- S E L E C T  A L L  R E Q U I R E D  F I E L D S  I N T O
	-- T E M P  T A B L E
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	SELECT 
		SAB.nBudgetId as nBudgetId, 
		SAB.FolderNumber as FolderNumber, 
		a.Id as AgrAgreementId,
		a.AgrBudgetContainerId as AgrBudgetContainerId,
		ABT.Id as AgrBudgetTypeId,
		BVT.Id as AgrBudgetViewTypeId,
		AFM.Id as AgrFundAgreeMapId,
		Datediff(m,ISNULL(SAB.AgrBudgetStartDate, 0),ISNULL(SAB.AgrBudgetEndDate, 0)) as CalculatedMonths,
		isnull(SAB.ICRate2,SAB.ICRate1) as ICRate2,		
		AF.Id as AgrFundId,
		coalesce(SAB.AgrBudgetStartDate,A.ProjectStartDate,'1/1/1900') as AgrBudgetStartDate,
		coalesce(SAB.AgrBudgetEndDate,A.ProjectEndDate,'12/31/2099') as AgrBudgetEndDate		
	INTO #AgrBudgetHeader
	FROM 
		Stg_IE_AgrBudget_Ctl  SAB
		inner join AgrAgreement a on SAB.Prop_No = A.PropNumber --a.FolderNumber = SAB.FolderNumber 
--		and a.documentnumber = (SELECT max(a1.documentNumber) FROM AgrAgreement a1
--							WHERE a1.foldernumber = a.foldernumber 																
--							and a1.isAmendment = 0 and a1.IsCurrent=1)
		inner join AgrBudgetViewType BVT ON BVT.Name = SAB.BudgetView		
		Left Outer Join AgrFund AF on AF.FundNumber = SAB.sPSFundNumber
		Left Outer Join AgrFund_AgrAgreement_Map AFM ON AFM.AgrFundId = AF.Id		
		inner join AgrBudgetType ABT ON ABT.Name = SAB.Insight_Awd_Type
		--Left Outer Join AgrSubcontractor ASUB ON ASUB.InfoEdSubcontractorID = SAB.nSubContractorId
	

		
	UPDATE SAB 
	Set
		AgrSubContractorID = ASUB.Id
	FROM
		Stg_IE_AgrBudget_Ctl SAB 		
		inner join AgrSubcontractor ASUB ON ASUB.InfoEdInstCode = SAB.nSubContractorId
	WHERE
		SAB.nSubContractorId is not null
		


	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	M A P  I D S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
 
	UPDATE Stg_IE_AgrBudget_Ctl  
	SET 
		AgrAgreementId = ABH.AgrAgreementId,
		AgrBudgetTypeId = ABH.AgrBudgetTypeId,		
		AgrFundAgrAgreementMapId = ABH.AgrFundAgreeMapId,
		AgrBudgetContainerId = ABH.AgrBudgetContainerId,
		AgrBudgetViewTypeId = ABH.AgrBudgetViewTypeId,		
		AgrBudgetStartDate = ABH.AgrBudgetStartDate,
		AgrBudgetEndDate = ABH.AgrBudgetEndDate,
		IsNewBudget = 1,	
		IsValid = 1
	FROM
		Stg_IE_AgrBudget_Ctl  SAB,
		#AgrBudgetHeader ABH 
	WHERE
		ABH.nBudgetId = SAB.nBudgetId 
	

	UPDATE 
		Stg_IE_AgrBudget_Ctl  
	SET 
		IsNewSubContractor = 1 --CASE WHEN SAB.nSubContractorId is not null THEN 1 ELSE NULL
	FROM
		Stg_IE_AgrBudget_Ctl SAB
	WHERE
		nSubContractorId is not null
		and AgrSubContractorId is not null -- They should be matched otherwise we can not insert them.

	

	

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	E X I S T I N G  B U D G E T  H E A D E R  R E C O R D S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/

	UPDATE Stg_IE_AgrBudget_Ctl  
	SET 
		IsNewBudget = 0,
		AgrBudgetContainerViewId = ABCV.Id,
		AgrBudgetId = AB.Id
	FROM 
		Stg_IE_AgrBudget_Ctl  SAB
		inner join AgrAgreement a on SAB.Prop_No = A.PropNumber --a.FolderNumber = SAB.FolderNumber 
--		and a.documentnumber = (SELECT max(a1.documentNumber) FROM AgrAgreement a1
--							WHERE a1.foldernumber = a.foldernumber 																
--							and a1.isAmendment = 0 and a1.IsCurrent=1)
		inner join  AgrBudgetContainer ABC ON ABC.Id = a.AgrBudgetContainerId
		inner join  AgrBudgetContainerView ABCV ON ABCV.AgrBudgetContainerId = SAB.AgrBudgetContainerId
											and ABCV.AgrBudgetViewTypeId = SAB.AgrBudgetViewTypeId
		inner join  AgrBudget AB ON AB.AgrBudgetContainerViewId = ABCV.Id
									and AB.Year = SAB.Year	
									and AB.AgrBudgetTypeID = SAB.AgrBudgetTypeID

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	D E T E C T  E R R O R S
	Look for stuff that does not map
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/

	UPDATE	Stg_IE_AgrBudget_Ctl  
	SET		IsValid = 0
	WHERE
		   isNull(nBudgetId,'') = ''
		or isNull(FolderNumber,'') = '' 
		or isNull(AgrAgreementId,0) = 0 
		or isNull(Year,0) = 0
		or AgrBudgetStartDate is null
		or AgrBudgetEndDate is null
		or isNull(AgrBudgetContainerId,0) = 0			
		or isNull(AgrBudgetTypeId,0) = 0		


	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	P R E P A R E  F O R  A G R B U D G E T C O N T A I N E R V I E W  T A B L E
	F O R  N O N S U B C O N T R A C T O R
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	--Get all distinct ContainerIds and View types
	SELECT DISTINCT 
		AgrBudgetContainerId, 
		AgrBudgetViewTypeId
	INTO 
		#DistBudgetContainerView
	FROM
		stg_IE_AgrBudget_Ctl
	WHERE
		IsValid = 1 
		and IsNewBudget = 1
		and nSubContractorId is null
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S E E D  I D S F O R C O N T A I N E R  V I E W
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	--AgrBudgetContainerViewId for Non SubContractor
	IF (SELECT Count(Id) FROM AgrBudgetContainerView) = 0
		SET	@agrBudgetContainerViewIdSeed = 1
	ELSE
		SET	@agrBudgetContainerViewIdSeed = IDENT_CURRENT('AgrBudgetContainerView') + 1
	

	
	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAgrBudgetContainerViewIDs')
		DROP TABLE tmpSeededAgrBudgetContainerViewIDs
	
	
	SET @sql = 'SELECT IDENTITY(int, ' + CAST(@agrBudgetContainerViewIdSeed AS varchar) + ', 1) AS AgrBudgetContainerViewId, AgrBudgetContainerId, AgrBudgetViewTypeId INTO tmpSeededAgrBudgetContainerViewIDs FROM #DistBudgetContainerView'
	EXEC(@sql)
	
	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAgrBudgetContainerViewIDs') 
	Begin
		UPDATE	SAB
		SET		SAB.AgrBudgetContainerViewId = T.AgrBudgetContainerViewID
		FROM	Stg_IE_AgrBudget_Ctl SAB
				inner join tmpSeededAgrBudgetContainerViewIDs T 
									on T.AgrBudgetContainerId = SAB.AgrBudgetContainerId
									and T.AgrBudgetViewTypeId = SAB.AgrBudgetViewTypeId
		WHERE	nSubContractorId is null
		DROP TABLE dbo.tmpSeededAgrBudgetContainerViewIDs
	End

	DROP TABLE #DistBudgetContainerView

	SELECT	distinct AgrBudgetContainerViewId as AgrBudgetContainerViewId, 			
			Max(Year)as Year			
	INTO	#DistBudgetContainerviewYear
	FROM	stg_IE_AgrBudget_Ctl
	WHERE	
			IsValid = 1 
			and IsNewBudget = 1
			and nSubContractorId is null
	GROUP BY AgrBudgetContainerViewId

	--Get the distinct AgrBudgetContainerViewId field
	INSERT INTO	Stg_IE_AgrBudgetContainerView_Ctl			
			(AgrBudgetContainerViewId, 
			AgrBudgetContainerId,
			AgrBudgetViewTypeId, 
			TargetPatientNumber,
			PerCapitaValue, 
			InflationRate,
			Year)
	SELECT	distinct
			SAB.AgrBudgetContainerViewId,
			max(SAB.AgrBudgetContainerId), 
			max(SAB.AgrBudgetViewTypeId), 
			max(SAB.TargetPatientNumber),
			max(SAB.PerCapitaValue),
			max(SAB.InflationRate),
			max(SAB.Year)
	FROM 
			stg_IE_AgrBudget_Ctl SAB
			inner join #DistBudgetContainerviewYear DBCY 
								on DBCY.AgrBudgetContainerViewId = SAB.AgrBudgetContainerViewId
								and DBCY.Year	= SAB.Year
	
	GROUP BY SAB.AgrBudgetContainerViewId

	DROP TABLE #DistBudgetContainerviewYear

	
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S E E D  I D S  F O R  A G R B U D G E T
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	--AgrBudgetId
	IF (SELECT Count(Id) FROM AgrBudget) = 0
		SET	@agrBudgetIdSeed = 1
	ELSE
		SET	@agrBudgetIdSeed = IDENT_CURRENT('AgrBudget') + 1

	
	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAgrBudgetIDs')
		DROP TABLE tmpSeededAgrBudgetIDs

	SET	@sql = 'SELECT	IDENTITY(int, ' + CAST(@agrBudgetIdSeed AS varchar) + ', 1) AS AgrBudgetId, nBudgetId INTO tmpSeededAgrBudgetIDs FROM Stg_IE_AgrBudget_Ctl  where IsNewBudget = 1 and IsValid = 1'
	EXEC(@sql)
	

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAgrBudgetIDs') 
	Begin
		UPDATE	Stg_IE_AgrBudget_Ctl 
		SET		AgrBudgetId = tmpSeededAgrBudgetIDs.AgrBudgetId
		FROM	tmpSeededAgrBudgetIDs
		WHERE	Stg_IE_AgrBudget_Ctl.nBudgetId = tmpSeededAgrBudgetIDs.nBudgetId
		
		DROP TABLE dbo.tmpSeededAgrBudgetIDs
	End


	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	A G R B U D G E T I C R A T E  R E C O R D S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	--Take care of 1st rate record
	INSERT INTO
		Stg_IE_AgrBudgetICRate
		(AgrBudgetICRateId, nBudgetId,
		AgrBudgetId, Rate,
		NumberOfMonths, SequenceNo,
		EffectiveDate, IsValid,
		IsNewRecord)	
	SELECT
		NULL as AgrBudgetICRateId,
		nBudgetId as nBudgetId,
		AgrBudgetId as AgrBudgetId,
		ICRate1 as Rate,
		MonthsRate as NumberOfMonths,
		1 as SequenceNo,
		NULL as EffectiveDate,
		IsValid as IsValid,
		1 as IsNewRecord
	FROM
		Stg_IE_AgrBudget_Ctl 
	
	--Now take care of 2nd rate if Budget months is greater than MonthsRate which is coming from Header feed.
	INSERT INTO
		Stg_IE_AgrBudgetICRate
		(AgrBudgetICRateId, nBudgetId,
		AgrBudgetId, Rate,
		NumberOfMonths, SequenceNo,
		EffectiveDate, IsValid,
		IsNewRecord)	
	SELECT
		NULL as AgrBudgetICRateId,
		nBudgetId as nBudgetId,
		AgrBudgetId as AgrBudgetId,
		ICRate2 as Rate,
		(DATEDIFF(MONTH, SAB.AgrBudgetStartDate,SAB.AgrBudgetEndDate) - MonthsRate) as NumberOfMonths,
		2 as SequenceNo,
		NULL as EffectiveDate,
		IsValid as IsValid,
		1 as IsNewRecord
	FROM
		Stg_IE_AgrBudget_Ctl  SAB
	WHERE
		(DATEDIFF(MONTH, SAB.AgrBudgetStartDate,SAB.AgrBudgetEndDate) - MonthsRate) > 0 
		and SAB.IsValid = 1  --Take only valid records
		--AND SAB.ICRate1 > 0 Do we need this? There are some ICRate1 that is coming 0. Do we need to create AgrBudgetICRate record for 0?


	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	D E T E C T  E R R O R S
	Look for stuff that does not map
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	UPDATE	Stg_IE_AgrBudgetICRate  
	SET		IsValid = 0
	WHERE
		   isNull(nBudgetId,'') = ''		
		or isNull(AgrBudgetId,0) = 0 
		
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- B U D G E T  D E T A I L	
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- I N S E R T  M I S S I N G  A C C O U N T S
	-- Some of the ledger accounts are missing from the template.
	-- This will proactively attempt to insert any missing accounts
	-- into the template tables to increase mapping hits.
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	
	SELECT DISTINCT
		FA.Description AS Name,
		FA.Description,
		FA.Id AS FinAccountId
	INTO #Accounts
	FROM
		Stg_IE_AgrBudgetInfo_Ctl SABI
		left outer join FinAccount FA ON FA.Number = SABI.PSAccountNumber
		left outer join AgrBudgetItemAccount_Template ABIT ON ABIT.FinAccountId = FA.ID
		left outer join AgrBudgetItem_ContainerAccount_Template_Map ACTM ON ACTM.AgrAccountTemplateId = ABIT.Id
	WHERE ACTM.id is null and FA.Id is not null

	INSERT INTO	AgrBudgetItemAccount_Template (Name,Description,FinAccountId)
	SELECT Name, Description, FinAccountId FROM #Accounts

	/* We have to insert two rows per account into this table: one for normal budgets and one for subcontractors. */

	INSERT INTO AgrBudgetItem_ContainerAccount_Template_Map(AgrAccountTemplateId,AgrContainerTemplateId,SequenceNumber)
	SELECT DISTINCT
		ABIT.Id AS AgrAccountTemplateId,
		1 AS AgrContainerTemplateId,
		ABIT.Id AS SequenceNumber
	FROM
		AgrBudgetItemAccount_Template ABIT
		inner join #Accounts A ON A.FinAccountId = ABIT.FinAccountId

	INSERT INTO AgrBudgetItem_ContainerAccount_Template_Map(AgrAccountTemplateId,AgrContainerTemplateId,SequenceNumber)
	SELECT DISTINCT
		ABIT.Id AS AgrAccountTemplateId,
		2 AS AgrContainerTemplateId,
		ABIT.Id AS SequenceNumber
	FROM
		AgrBudgetItemAccount_Template ABIT
		inner join #Accounts A ON A.FinAccountId = ABIT.FinAccountId

	DROP TABLE #Accounts

	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- G E T  A L L  I D S
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	
	UPDATE Stg_IE_AgrBudgetInfo_Ctl  
	SET 
		AgrBudgetId = SAB.AgrBudgetId,
		IsValid = SAB.IsValid,
		IsNewRecord = 1
	FROM
		Stg_IE_AgrBudgetInfo_Ctl  SABD
		inner join Stg_IE_AgrBudget_Ctl  SAB ON SABD.nBudgetId = SAB.nBudgetId
		
	
	--AgrContainerAccountTemplateMapId and FinAccountId

	UPDATE Stg_IE_AgrBudgetInfo_Ctl  
	SET 
		AgrContainerAccountTemplateMapId = ACTM.Id,
		FinAccountId = FA.Id
	FROM
		Stg_IE_AgrBudgetInfo_Ctl  SABI
		inner join FinAccount FA ON FA.Number = SABI.PSAccountNumber
		inner join AgrBudgetItemAccount_Template ABIT ON ABIT.FinAccountId = FA.ID
		inner join AgrBudgetItem_ContainerAccount_Template_Map ACTM ON ACTM.AgrAccountTemplateId = ABIT.Id

	
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	D E T E C T  E R R O R S
	Look for stuff that does not map
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/ 
	--select * from Stg_IE_AgrBudgetInfo_Ctl
	UPDATE	Stg_IE_AgrBudgetInfo_Ctl  
	SET		IsValid = 0
	WHERE
		   isNull(nBudgetDetailId,'') = ''
		or isNull(nBudgetId,'') = ''
		or isNull(AgrBudgetId,0) = 0 
		or isNull(PSAccountNumber,0) = 0 								
		or isNull(AgrContainerAccountTemplateMapId,0) = 0
		or isNull(FinAccountId,0) = 0
		
	
	
	DROP TABLE #AgrBudgetHeader

	-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	--S U B C O N T R A C T
	-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

	/********************************************************************/
	/*Take care of New Budget Container for SubContractor records		*/

	UPDATE 
		Stg_IE_AgrBudget_Ctl  
	SET 
		AgrBudgetContainerId = null,
		isValid = 1
	WHERE
		nSubContractorId is not null


	--Create New AgrBudgetContainer 
	SELECT 
		distinct SAB.AgrAgreementId, SAB.AgrSubContractorId, SABI.FinAccountId
	INTO
		#DistSubContractor --tempSubContractor --
	FROM
		Stg_IE_AgrBudget_Ctl SAB
		inner join Stg_IE_AgrBudgetInfo_Ctl SABI on SABI.nBudgetId = SAB.nBudgetId
	WHERE
		SAB.IsValid = 1
		and SABI.IsValid = 1 
		--and IsNewBudget = 1
		and IsNewSubContractor = 1


	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S E E D  I D S F O R  A G R B U D G E T C O N T A I N E R 
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	IF (SELECT Count(Id) FROM AgrBudgetContainer) = 0
		SET	@agrBudgetContainerIdSeed = 1
	ELSE
		SET	@agrBudgetContainerIdSeed = IDENT_CURRENT('AgrBudgetContainer') + 1
	


	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAgrBudgetContainerIDs')
		DROP TABLE tmpSeededAgrBudgetContainerIDs

	
	SET @sql = 'SELECT IDENTITY(int, ' + CAST(@agrBudgetContainerIdSeed AS varchar) + ', 1) AS AgrBudgetContainerId, AgrAgreementId, FinAccountId, AgrSubContractorId INTO tmpSeededAgrBudgetContainerIDs FROM #DistSubContractor'
	EXEC(@sql)
	

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAgrBudgetContainerIDs') 
	Begin
		INSERT INTO	Stg_IE_AgrBudgetContainer_Ctl
			(AgrBudgetContainerId, 	
			[Name], 
			Description,
			AgrAgreementId, 
			FinAccountId,
			AgrSubContractorId,
			IndirectCostBaseId,
			AgrBudgetTemplateTypeId)
		SELECT
			AgrBudgetContainerId,
			'SubContractor Budget Container: ' + Cast(AgrSubContractorId AS varchar(10)),
			'SubContractor budget Container for ' + Cast(AgrSubContractorId AS varchar(10)),
			AgrAgreementId, 
			FinAccountId,
			AgrSubContractorId,
			null,
			2		
		FROM
			tmpSeededAgrBudgetContainerIDs

		DROP TABLE dbo.tmpSeededAgrBudgetContainerIDs
	End


	DROP TABLE #DistSubContractor



	SELECT	SAB.AgrBudgetContainerId,SAB.AgrAgreementId, SAB.nBudgetId,
		SAB.AgrSubContractorID, SABC.FinAccountId 
	INTO 
		#SubContractorContainer1
	FROM
		Stg_IE_AgrBudget_Ctl SAB
		--inner join Stg_IE_AgrBudgetInfo_Ctl SABI on SABI.nBudgetId = SAB.nBudgetID
													--and SABI.FinAccountId = SABC.FinAccountId	
												
		inner join Stg_IE_AgrBudgetContainer_Ctl SABC on SABC.AgrAgreementId = SAB.AgrAgreementId
													and  SABC.AgrSubContractorID = SAB.AgrSubContractorID
	WHERE
		SAB.IsValid = 1 		
		and SAB.IsNewSubContractor = 1

	SELECT 
		ABC.AgrBudgetContainerId,SCC.AgrAgreementId, SCC.nBudgetId,
		SCC.AgrSubContractorID, SCC.FinAccountId
	INTO
		#SubContractorContainer2
	FROM 
		#SubContractorContainer1 SCC
		inner join Stg_IE_AgrBudgetContainer_Ctl ABC on ABC.AgrAgreementID = SCC.AgrAgreementId
											and ABC.AgrSubContractorId = SCC.AgrSubContractorID
											and ABC.FinAccountId = SCC.FinAccountId

	UPDATE
		SAB
	SET
		SAB.AgrBudgetContainerId = SCC.AgrBudgetContainerId
	FROM
		Stg_IE_AgrBudget_Ctl SAB
		inner join #SubContractorContainer2 SCC on SCC.nBudgetId = SAB.nBudgetId
	
	DROP TABLE #SubContractorContainer1	
	DROP TABLE #SubContractorContainer2
	

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	P R E P A R E  F O R  A G R B U D G E T C O N T A I N E R V I E W  T A B L E
	F O R S U B C O N T R A C T O R
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	--Get all distinct ContainerIds and View types
	SELECT DISTINCT 
		AgrBudgetContainerId, 
		AgrBudgetViewTypeId
	INTO 
		#DistBudgetContainerViewSubCon
	FROM
		stg_IE_AgrBudget_Ctl
	WHERE
		IsValid = 1 
		and IsNewBudget = 1
		and nSubContractorId is not null
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	S E E D  I D S
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/
	--AgrBudgetContainerViewId for Non SubContractor
	/*IF (SELECT Count(Id) FROM AgrBudgetContainerView) = 0
		SET	@agrBudgetContainerViewIdSeed = 1
	ELSE
		SET	@agrBudgetContainerViewIdSeed = IDENT_CURRENT('AgrBudgetContainerView') + 1
	*/

	SELECT @agrBudgetContainerViewIdSeed  = max(AgrBudgetContainerViewId) + 1 from Stg_IE_AgrBudgetContainerView_Ctl

	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAgrBudgetContainerViewIDs')
		DROP TABLE tmpSeededAgrBudgetContainerViewIDs
	
	
	SET @sql = 'SELECT IDENTITY(int, ' + CAST(@agrBudgetContainerViewIdSeed AS varchar) + ', 1) AS AgrBudgetContainerViewId, AgrBudgetContainerId, AgrBudgetViewTypeId INTO tmpSeededAgrBudgetContainerViewIDs FROM #DistBudgetContainerViewSubCon'
	EXEC(@sql)
	
	IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tmpSeededAgrBudgetContainerViewIDs') 
	Begin
		UPDATE	SAB
		SET		SAB.AgrBudgetContainerViewId = T.AgrBudgetContainerViewID
		FROM	Stg_IE_AgrBudget_Ctl SAB
				inner join tmpSeededAgrBudgetContainerViewIDs T 
									on T.AgrBudgetContainerId = SAB.AgrBudgetContainerId
									and T.AgrBudgetViewTypeId = SAB.AgrBudgetViewTypeId
		WHERE	nSubContractorId is not null
		DROP TABLE dbo.tmpSeededAgrBudgetContainerViewIDs
	End

	DROP TABLE #DistBudgetContainerViewSubCon

	SELECT	distinct AgrBudgetContainerViewId as AgrBudgetContainerViewId, 			
			Max(Year)as Year			
	INTO	#DistBudgetContainerviewYearSubCon
	FROM	stg_IE_AgrBudget_Ctl
	WHERE	
			IsValid = 1 
			and IsNewBudget = 1
			and nSubContractorId is not null
	GROUP BY AgrBudgetContainerViewId

	--Get the distinct AgrBudgetContainerViewId field
	INSERT INTO	Stg_IE_AgrBudgetContainerView_Ctl			
			(AgrBudgetContainerViewId, 
			AgrBudgetContainerId,
			AgrBudgetViewTypeId, 
			TargetPatientNumber,
			PerCapitaValue, 
			InflationRate,
			Year)
	SELECT	distinct
			SAB.AgrBudgetContainerViewId,
			max(SAB.AgrBudgetContainerId), 
			max(SAB.AgrBudgetViewTypeId), 
			max(SAB.TargetPatientNumber),
			max(SAB.PerCapitaValue),
			max(SAB.InflationRate),
			max(SAB.Year)
	FROM 
			stg_IE_AgrBudget_Ctl SAB
			inner join #DistBudgetContainerviewYearSubCon DBCY 
								on DBCY.AgrBudgetContainerViewId = SAB.AgrBudgetContainerViewId
								and DBCY.Year	= SAB.Year
	
	GROUP BY SAB.AgrBudgetContainerViewId

	DROP TABLE #DistBudgetContainerviewYearSubCon


	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	-- A G R E E M E N T  S U B C O N T R A C T O R  M A P	
	-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>	

	INSERT INTO
		Stg_IE_AgrAgreementSubcontractor_Map
		(AgrAgreementSubConMapId, AgrAgreementId,
		AgrSubContractorId, AgrBudgetContainerId,
		FinAccountId, IsNewSubContractor,
		IsValid)
	SELECT
		null as AgrAgreementSubConMapId,
		SABC.AgrAgreementId as AgrAgreementId,
		SABC.AgrSubContractorId as AgrSubContractorId,
		SABC.AgrBudgetContainerId as AgrBudgetContainerId, 
		SABC.FinAccountId as FinAccountId,
		1 as IsNewSubContractor,
		1 as IsValid
	FROM
		Stg_IE_AgrBudgetContainer_Ctl  SABC
		

	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	D E T E C T  E R R O R S
	Look for stuff that does not map
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/

	UPDATE	Stg_IE_AgrBudget_Ctl  
	SET		IsValid = 0
	WHERE
		   isNull(nBudgetId,'') = ''
		or isNull(FolderNumber,'') = '' 
		or isNull(AgrAgreementId,0) = 0 
		or isNull(Year,0) = 0
		or AgrBudgetStartDate is null
		or AgrBudgetEndDate is null
		or isNull(AgrBudgetContainerId,0) = 0			
		or isNull(AgrBudgetTypeId,0) = 0
		
	--	Update IsValid to 0 when items are invalid
		update Stg_IE_AgrBudget_Ctl set IsValid = 0 where  nBudgetId in (select distinct(nBudgetId) Stg_IE_AgrBudgetInfo_Ctl where IsValid = 0)
	SET NOCOUNT OFF

END


GO
